# 虚拟环境位置 -- D:\virtualENV3.5.2\ENV
# -*- coding: utf-8 -*-
# @Time    : 2017/11/29 20:58
# @Author  : wdm
# @Site    : 
# @File    : word_count.py
# @Software: PyCharm

# 敏感词出现的次数

from html_data_dao import get_data_by_ischeck
from html_data_dao import update_ischeck
import pymysql.cursors


config = {
    'host': '106.14.140.96',
    'port': 3306,
    'user': 'bjhzyl',
    'password': 'bjhzyl2017',
    'db': 'base_db2',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

# Connect to the database
db = pymysql.connect(**config)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT c_sensitive_word_name, b_grade FROM bas_sensitive_word;")

# 使用 fetchone() 方法获取单条数据.
sensitive_word_name_list = cursor.fetchall()# 敏感词次数

while True:
    data_list_db = get_data_by_ischeck()# 数据库数据

    for _data in data_list_db:
        insert_data = []
        for sensitive_word in sensitive_word_name_list:
            _insert = []
            # 敏感词
            sensitive_word_name = sensitive_word["c_sensitive_word_name"]
            # 关键词分数
            fraction = sensitive_word["b_grade"]
            # 页面内容
            html_content = _data['content']
            word_count = str(html_content).count(sensitive_word_name, 0 , len(html_content))
            if word_count == 0:
                continue
            # 计算分数
            fraction_final = int(fraction) * int(word_count)

            _insert.append(_data["batch_id"])
            _insert.append(sensitive_word_name)
            _insert.append(_data["index_url"])
            _insert.append(_data["main_url"])
            _insert.append(_data["title"])
            _insert.append(fraction_final)
            _insert.append(word_count)
            _insert.append(fraction)
            _insert.append(str(_data["_id"]))
            insert_data.append(str(_insert).replace("[","").replace("]",""))
        if len(insert_data) == 0:
            continue
        insert_sql = []
        insert_sql.append('INSERT INTO '
                      'cra_parse_result '
                      '( c_batch, c_word, c_url, site_access_url, c_title, total_score, appear_count, score, hash_code, create_time)'
                      'VALUES ')
        for index, data_sql in enumerate(insert_data):
            sql = "({}".format(data_sql)
            insert_sql.append(sql)
            insert_sql.append(",NOW( ))")
            if index == len(insert_data) - 1:
                insert_sql.append(";")
            else:
                insert_sql.append(",")
        print("".join(insert_sql))
        cursor.execute("".join(insert_sql))
        db.commit()
        #
        #  修改mongo数据字段  ischeck

        update_ischeck(_data["_id"])


# 关闭数据库连接
db.close()

